package me
import grails.converters.JSON
import com.mysql.cj.jdbc.MysqlConnectionPoolDataSource
import groovy.sql.Sql
import groovy.json.*
import groovy.json.JsonSlurper
import grails.plugins.rest.client.RestBuilder
import oracle.jdbc.pool.OracleDataSource;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import net.sourceforge.jtds.jdbcx.JtdsDataSource;
class DatasourceController {

	def dataSource

    def index() { 

    	
    }

  	def store() {
		def sql = new Sql(dataSource)
   		def results = [:]	  
    	def rows = sql.rows("select * from data_source_hy_info")
    	results.total = rows.size()
    	results = [success:true,total:rows.size(),results:rows]


      /*def rest = new RestBuilder()
      def resp = rest.get("http://120.55.58.28/rfidApi/carQueue")
      def json = resp.json
      println json*/

    	render results as JSON
  	}


  	def delete() {
  		def ds = DataSourceHyInfo.findById(params.id)
  		println ds
  		ds.delete(flush: true) 
  		def results = [success:true]
    	render results as JSON
  	}

    def testconnection() {
      def results = [success:true]
      def rows = []
      def ds 
    	def sql = new Sql(dataSource)
    	def dataSourceInfo = sql.rows("select * from data_source_hy_info where id = ?",params.id)[0]
    	def ip = dataSourceInfo.ip
      def dsType = dataSourceInfo.ds_type
    	def db = dataSourceInfo.db
    	def port = dataSourceInfo.port
    	def userName = dataSourceInfo.userloginid
    	def password = dataSourceInfo.userpassword
		  def testSql = ""
      if (dsType=="MySql") {
        testSql = "select 1"
        ds = new MysqlConnectionPoolDataSource();
        ds.setUser(userName);
		    ds.setPassword(password);
		    ds.setURL("jdbc:mysql://"+ip+":"+port+"/"+db+"?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8");
      }

      if (dsType=="Oracle") {
        testSql = "select 1 from dual"
        ds = new OracleDataSource();
        ds.setDriverType("thin");
        ds.setNetworkProtocol("tcp");
        ds.setServerName(ip);
        ds.setDatabaseName(db);
        ds.setPortNumber(port as Integer);
        ds.setUser(userName); 
        ds.setPassword(password);
        //ds.setURL("jdbc:mysql://"+ip+":"+port+"/"+db+"?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8");
      }

      if (dsType=="SqlServer") {
        testSql = "select 1"
        /*ds = new SQLServerDataSource();
        ds.setUser(userName);
        ds.setPassword(password);
        ds.setServerName(ip);
        ds.setPortNumber(port as Integer);
        ds.setDatabaseName(db);
        */
        ds = new JtdsDataSource();
        ds.setServerName(ip);
        ds.setDatabaseName(db);
        ds.setUser(userName);
        ds.setPassword(password);
      }

		  def dSql = new Sql(ds)
		  def printColNames = { meta ->}
		  def printRow = { row ->}
     	try {
    		  dSql.eachRow(testSql, printColNames, printRow)
		  } catch (e) {
			   results = [success:false,message:e.getMessage()]
			   println e.getMessage()
		  }
    	render results as JSON 
  	}

  	def save() {
  		def sql = new Sql(dataSource)
   		def resuls = [code:0,message:'success']
   		def dsId = params.id

   		if (dsId==null || dsId=="") {
   			def ds = new DataSourceHyInfo(ip: params.ip, db: params.db, name:params.name,
   			port: params.port,userloginid: params.userLoginId,
   			userpassword: params.password,dsType: params.dbType)
			 ds.save(failOnError: true)
   			//sql.execute("insert into data_source_hy_info()")
   		
   		} else {
   			def ds = DataSourceHyInfo.findById(dsId)
   			println ds
   			ds.ip = params.ip    
   			ds.db = params.db     
   			ds.name = params.name 
   			ds.dsType = params.dbType
   			ds.port = params.port as Integer
   			ds.userloginid = params.userLoginId
   			ds.userpassword = params.password
   			try {
  			    ds.save(failOnError: true,flush: true) 
  			}
  			catch (e) {
  			    println e
  			}
   		}
    	
    	//def rows = sql.execute("inser into data_source_hy_info()")

    	render resuls as JSON
  	}

}
